create database eprint2;

use eprint2;

CREATE TABLE `lastproc` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `timeinsert` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `lastproc` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3958 DEFAULT CHARSET=latin1;

CREATE TABLE `view` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `uniquebits` varchar(200) DEFAULT NULL,
  `archive_name` varchar(50) NOT NULL DEFAULT '',
  `ip` varchar(100) NOT NULL DEFAULT '',
  `request_date` date NOT NULL DEFAULT '0000-00-00',
  `archiveid` int(10) NOT NULL DEFAULT '0',
  `country_code` varchar(4) DEFAULT NULL,
  `country_name` varchar(100) DEFAULT NULL,
  `view_type` ENUM('abstract','download') DEFAULT NULL,
  `eprint_name` varchar(200) DEFAULT NULL,
	`latitude` DECIMAL(6,4)DEFAULT NULL,
	`longitude` DECIMAL(7,4)DEFAULT NULL,
	`city` varchar(100) DEFAULT NULL,
	`region` char(2) DEFAULT NULL,
	`other` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=732629 DEFAULT CHARSET=latin1;

flush privileges;
grant all privileges on eprint2.* to eprint2priv identified by 'private';
grant select on eprint2.* to eprint2pub identified by 'public';
flush privileges;

CREATE TRIGGER `eprint2`.`Location_Finder` BEFORE INSERT
    ON eprint2.view FOR EACH ROW
BEGIN
    declare ip, ip_1, ip_2, ip_3,ip_4 varchar(100);
		declare ipd, ipd_1, ipd_2, ipd_3, ipd_4 int(10) unsigned;
		declare city_n, country_n varchar(100);
		declare region_n char(2);
		declare long_n decimal(7,4);
		declare lat_n decimal(6,4);
		
		set ip = new.ip;
		set ip_1 = SUBSTRING(ip, 1, INSTR(ip, '.') - 1);
		set ip = SUBSTRING(ip, INSTR(ip, '.') + 1);
		set ip_2 = SUBSTRING(ip, 1, INSTR(ip, '.') - 1);
		set ip = SUBSTRING(ip, INSTR(ip, '.') + 1);
		set ip_3 = SUBSTRING(ip, 1, INSTR(ip, '.') - 1);
		set ip_4 = SUBSTRING(ip, INSTR(ip, '.') + 1);
		
		set ipd_1 = CONVERT(ip_1, unsigned);
		set ipd_2 = CONVERT(ip_2, unsigned);
		set ipd_3 = CONVERT(ip_3, unsigned);
		set ipd_4 = CONVERT(ip_4, unsigned);
		
		set ipd_1 = ipd_1 * POW(256, 3);
		set ipd_2 = ipd_2 * POW(256, 2);
		set ipd_3 = ipd_3 * 256;
		
		set ipd = ipd_1 + ipd_2 + ipd_3 + ipd_4;
		
		select geoip.Location.city, geoip.location.region, geoip.country.country, geoip.Location.latitude, geoip.Location.longitude into city_n, region_n, country_n, lat_n, long_n
		from (geoip.Location inner join geoip.country on geoip.Location.country = geoip.country.cc)
		where geoip.Location.locId = 
				(select geoip.Blocks.locId from geoip.Blocks where geoip.Blocks.start_ip = 
						(select max(geoip.Blocks.start_ip) from geoip.Blocks where geoip.Blocks.start_ip < ipd));
						
		if(new.country_code like 'X%') then set new.other = new.country_name;
		else set new.other = 'Normal Usage';
		end if;
		
		set new.city = city_n;
		set new.region = region_n;
		set new.country_name = country_n;
		set new.longitude = long_n;
		set new.latitude = lat_n; 
END;

commit;

use eprint2;